﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Data;

using CqusInfo.CR.SqlHelper;
using CqusInfo.RAL.Question;

namespace CqusInfo.DAL.Question
{
    public class QuestionDAL:Inteface.IQuestionDAL
    {
        private List<QuestionModel> roleList = new List<QuestionModel>();
        public Int32 GetCounts(bool isDel)
        {
            return SQLiteHelper.GetCounts("tb_Question", isDel);
        }
        /// <summary>
        /// 得到参数
        /// </summary>
        /// <param name="model">数据集</param>
        /// <returns>parameters</returns>
        private SQLiteParameter[] GetParameters(string qId, QuestionModel model)
        {
            SQLiteParameter[] parameters ={
                                         new SQLiteParameter("@qId",DbType.String),
                                         new SQLiteParameter("@userName",DbType.String),
                                         new SQLiteParameter("@content",DbType.String),
                                         new SQLiteParameter("@title",DbType.String),
                                         new SQLiteParameter("@dateTime",DbType.DateTime),
                                         new SQLiteParameter("@type",DbType.Int32,4)
                                         };
            parameters[0].Value = qId == null ? Guid.NewGuid().ToString() : qId;
            parameters[1].Value = model.c_vc_userName;
            parameters[2].Value = model.c_vc_content;
            parameters[3].Value = model.c_vc_title;
            parameters[4].Value = model.c_dt_dateTime;
            parameters[5].Value = model.c_i_type;

            return parameters;
        }

        public Int32 Add(QuestionModel model)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("insert into tb_Question values(");
            sqlStr.Append("c_vc_qId=@qId,c_vc_userName=@userName,c_vc_content=@content,c_vc_title=@title,c_dt_dateTime=@dateTime,c_i_type=@type,c_b_isReplay=" + false + ",c_i_readNO=" + 0 + ",c_b_isDel=" + false + ")");

            return SQLiteHelper.ExecuteNonQuery(sqlStr.ToString(), GetParameters(null, model));
        }

        public Int32 Update(string qId, QuestionModel model)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("update tb_Question set ");
            sqlStr.Append("c_vc_userName=@userName,c_vc_content=@content,c_vc_title=@title,c_dt_dateTime=@dateTime,c_i_type=@type where c_vc_qId=@qId and c_b_isReplay=false and c_b_isDel=false");

            return SQLiteHelper.ExecuteNonQuery(sqlStr.ToString(), GetParameters(qId, model));
        }

        public Int32 Delete(string qId, bool isDel, bool trueDel)
        {
            if (trueDel)
                return SQLiteHelper.Delete("tb_Question", "c_vc_qId", qId);
            else
                return SQLiteHelper.Delete("tb_Question", "c_vc_qId", qId, "c_b_isDel", isDel);
        }
        /// <summary>
        /// 从数据库查找某一段时间的意见信息
        /// </summary>
        /// <param name="startTime">开始时间</param>
        /// <param name="endTime">结束时间</param>
        /// <param name="isDel">true：已删除 false：未删除</param>
        /// <param name="isReplay">true：已回复 false：未回复</param>
        /// <param name="page">页数，第几页 需要减一</param>
        /// <param name="takeItem">每页显示的数目</param>
        /// <returns>DataSet</returns>
        private DataSet Query_DB(DateTime startTime, DateTime endTime, bool isDel, bool isReplay, Int32 page, Int32 takeItem)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("select * from tb_Question limit " + (page - 1) * takeItem + "," + takeItem);
            sqlStr.Append(" where c_b_isReplay=@isRplay and c_b_isDel=@isDel and c_dt_datetime between @startTime and @endTime");

            SQLiteParameter[] parameters ={
                                        new SQLiteParameter("@startTime",DbType.DateTime),
                                        new SQLiteParameter("@endTime",DbType.DateTime),
                                        new SQLiteParameter("@isReplay",DbType.Boolean),
                                        new SQLiteParameter("@isDel",DbType.Boolean)
                                        };
            parameters[0].Value = startTime;
            parameters[1].Value = endTime;
            parameters[2].Value = isReplay;
            parameters[3].Value = isDel;

            return SQLiteHelper.ExecuteQuery(sqlStr.ToString(), parameters);
        }
        public List<QuestionModel> Query(DateTime startTime, DateTime endTime, bool isDel, bool isReplay, Int32 page, Int32 takeItem)
        {
            DataSet ds = Query_DB(startTime, endTime, isDel, isReplay, page, takeItem);
            string cacheId = string.Format("QuestionModel_{0}_{1}", startTime.ToShortDateString(),endTime.ToShortDateString());
            roleList = SqlData.GetQueryList<QuestionModel>(cacheId, "QuestionModel", ds);
            return roleList;
        }

        public List<QuestionModel> Query(string qId)
        {
            DataSet ds = SQLiteHelper.Query("tb_Question", "c_vc_qId", qId);
            string cacheId = string.Format("QuestionModel_{0}", qId);
            roleList = SqlData.GetQueryList<QuestionModel>(cacheId, "QuestionModel", ds);
            return roleList;
        }

        /// <summary>
        /// 从数据库查找某一个用户的意见信息
        /// </summary>
        /// <param name="userName">用户名</param>
        /// <param name="isDel">true：已删除 false：未删除</param>
        /// <param name="isReplay">true：已回复 false：未回复</param>
        /// <param name="page">页数，第几页 需要减一</param>
        /// <param name="takeItem">每页显示的数目</param>
        /// <returns>DataSet</returns>
        private DataSet Query_DB(string userName, bool isDel, bool isReplay, Int32 page, Int32 takeItem)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("select * from tb_Question limit " + (page - 1) * takeItem + "," + takeItem);
            sqlStr.Append(" where c_vc_userName=@userName and c_b_isReplay=@isRplay and c_b_isDel=@isDel");

            SQLiteParameter[] parameters ={
                                        new SQLiteParameter("@userName",DbType.String),
                                        new SQLiteParameter("@isReplay",DbType.Boolean),
                                        new SQLiteParameter("@isDel",DbType.Boolean)
                                        };
            parameters[0].Value = userName;
            parameters[1].Value = isReplay;
            parameters[2].Value = isDel;

            return SQLiteHelper.ExecuteQuery(sqlStr.ToString(), parameters);
        }
        public List<QuestionModel> Query(string userName, bool isDel, bool isReplay, Int32 page, Int32 takeItem)
        {
            DataSet ds = Query_DB(userName, isDel, isReplay, page, takeItem);
            string cacheId = string.Format("QuestionModel_{0}", userName);
            roleList = SqlData.GetQueryList<QuestionModel>(cacheId, "QuestionModel", ds);
            return roleList;
        }

        public List<QuestionModel> Query(Int32 page, Int32 takeItem, bool isDel,bool isReplay)
        {
            DataSet ds = SQLiteHelper.Query("tb_Question", "c_b_isReplay", isReplay, "c_b_isDel", isDel, page, takeItem);
            string cacheId = string.Format("QuestionModel_{0}_{1}_{2}", page, takeItem, isDel);
            roleList = SqlData.GetQueryList<QuestionModel>(cacheId, "QuestionModel", ds);
            return roleList;
        }
    }
}
